package com.wifiscanner;

/*
 * We put all the database stuff here 
 * 
 */

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteCursorDriver;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQuery;
import android.util.Log;


public class DBAdapter {

    private static final String DATABASE_NAME = "wifi";
    private static final int DATABASE_VERSION = 1;
    
    // sql commands used to create tables/indexes
    private static final String CREATE_ACCESS_POINT = 
    	"CREATE TABLE if not exists access_point (" +
    	"bssid text primary key, " +
    	"ssid text, " +
    	"capabilities text, " +
    	"level integer, " +
    	"frequency integer, " +
    	"lat real, " +
    	"lon real, " +
    	"alt real, " +
    	"timestamp integer)";
    
    private static final String CREATE_LATLON_INDEX = 
    	"CREATE INDEX if not exists latlon_idx on access_point(" +
    	"lat, lon)";
    
    private static final String SELECT_ACCESS_POINT = 
    	"SELECT bssid as _id," +
    	" ssid, capabilities,level,frequency," +
    	" lat,lon,alt,timestamp " +
    	"FROM access_point";
    
    public static String FILTER_LATLON = "lat >= ? and lat <= ? and lon >= ? and lon <= ?";
    public static String FILTER_BSSID   = "bssid = ?";
    public static String FILTER_ZEROFIX = "lat != 0 or lon !=0 or alt != 0";
    
	@SuppressWarnings("unused")
	private final Context context;
	private DBHelper helper;
	private SQLiteDatabase db;
	
	public DBAdapter(Context ctx) 
    {
        this.context = ctx;
        helper = new DBHelper(ctx);
    }
	
	
	public DBAdapter open() throws SQLException {
		db = helper.getWritableDatabase();
	    return this;
	}

	    //---closes the database---    
	public void close() {
		helper.close();
	}
		
	public AccessPoint getAccessPoint(String bssid) {
			
 		String [] args= new String[] { bssid };
		AccessPointCursor cursor = getAccessPoints(FILTER_BSSID, args);
		AccessPoint point;
		try {
			point = cursor.moveToFirst() ? cursor.getAccessPoint() : null;
		}
		finally {
			cursor.close();
		}
		return point;
	}
	
	public boolean setAccessPoint(AccessPoint point) {
		ContentValues values = new ContentValues();
		boolean done = false;
		if (point.isInserted()) {
			// update
			values.put("ssid", point.SSID);
			values.put("capabilities", point.capabilities);
			values.put("frequency", point.frequency);
			values.put("level", point.level);
			values.put("lat", point.lat);
			values.put("lon" , point.lon);
			values.put("alt", point.alt);
			values.put("timestamp", point.timestamp);
			String [] args= new String[] { point.BSSID };
			try {
				db.update("access_point", values, "bssid = ?", args);
				done = true;
			}
			catch (SQLException e) {
				Log.e("Error updatding bssid " + point.BSSID, e.toString());				
			}
		}
		else {
			// add
			values.put("bssid", point.BSSID);
			values.put("ssid", point.SSID);
			values.put("capabilities", point.capabilities);
			values.put("frequency", point.frequency);
			values.put("level", point.level);
			values.put("lat", point.lat);
			values.put("lon" , point.lon);
			values.put("alt", point.alt);
			values.put("timestamp", point.timestamp);
			try {
				 db.insert("access_point", null, values);
				 done = true;
				 point.setInserted(done);
			}
			catch (SQLException e) {
				Log.e("Error updatding bssid " + point.BSSID, e.toString());				
			}
		}
		return done;
	}

	public AccessPointCursor getAccessPoints(String filter, String[] args) {
		return getAccessPoints(filter, args, null);
	}
	
	public AccessPointCursor getAccessPoints(String filter, String[] args, String order) {
		
		StringBuilder sb = new StringBuilder();
		
		sb.append(SELECT_ACCESS_POINT);
		if (filter != null) {
			sb.append(" WHERE ");
			sb.append(filter);
		}
		if (order != null) {
			sb.append(" ORDER BY ");
			sb.append(order);
		}

		String sql = sb.toString();
		
        AccessPointCursor c = (AccessPointCursor) db.rawQueryWithFactory(
        		new AccessPointCursor.Factory(),
        		sql,
        		args,
        		null);
        
        return c;
	}
	
	private static class DBHelper extends SQLiteOpenHelper {
		
		private DBHelper(Context context)  {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
	    }

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_ACCESS_POINT);
			db.execSQL(CREATE_LATLON_INDEX);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
	
		}
	}
	
	public static class AccessPointCursor extends SQLiteCursor{
			private AccessPointCursor(SQLiteDatabase db, SQLiteCursorDriver driver, String editTable, SQLiteQuery query) {
	            super(db, driver, editTable, query);
	        }
	        private static class Factory implements SQLiteDatabase.CursorFactory{
	            @Override
	            public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver driver, String editTable, SQLiteQuery query) {
	                return new AccessPointCursor(db, driver, editTable, query);
	            }
	        }
	        public AccessPoint getAccessPoint() {
	        	AccessPoint point = new AccessPoint(true);
	        	// load from current db cursor position
				point.BSSID = getString(getColumnIndexOrThrow("_id"));
				point.SSID = getString(getColumnIndexOrThrow("ssid"));
				point.capabilities = getString(getColumnIndexOrThrow("capabilities"));
				point.level = getInt(getColumnIndexOrThrow("level"));
				point.frequency = getInt(getColumnIndexOrThrow("frequency"));
				point.lat = getDouble(getColumnIndexOrThrow("lat"));
				point.lon = getDouble(getColumnIndexOrThrow("lon"));
				point.alt= getDouble(getColumnIndexOrThrow("alt"));
	        	return point;	
	        }
	    }

	

}
